*******************************************************************************
***
*** Create the corporate credit dataset including borrower and lender characteristics
***
*** Dependencies from folder ${procdata}:
***		- urtevirk_panel.dta 
***		- firm_panel.dta
***		- fire_panel.dta
***		- macro_data.dta
***		- DK_USD.dta (in ${rawdata})
***
***	Output to folder ${procdata}:
***		- URTEVIRK_FIRE_FIRM_03-18.dta 
***
*******************************************************************************


////// Create final dataset
use "${procdata}/urtevirk_panel.dta", clear

* Sample selection
drop if KTOTYPE_KOD==54 | KTOTYPE_KOD==55 | KTOTYPE_KOD==59  //state-guaranteed loans or loans granted by municipality
drop if REST_GAELD_BLB < 0  // Borrower-lender-year pairs with negative debt
*drop loans that we cannot identify as typical bank and nonbank debt
drop if KTOTYPE_KOD<53
drop if KTOTYPE_KOD>57

** Mark loan delinquencies 
gen byte delinquency = (REST_MARK_KOD != 0) if !mi(REST_MARK_KOD)
clonevar REST_GAELD_BLB_withdelinq = REST_GAELD_BLB
replace REST_GAELD_BLB = . if delinquency == 1


////////////////////////////////////////////
//////////  Add information on borrowers and lenders from firm register

// Obtain lender information from FIRM 
rename cvrnr_lender cvrnr 
merge m:1 cvrnr year using "${procdata}/firm_panel.dta", keepusing(JUR_VIRK_FORM JUR_FRA_DATO JUR_TIL_DATO GF_NACE_DB07 JUR_HOVED_BRA_DB07 GF_ANSATTE GF_RFEP GF_AT GF_EGUL) keep(match master)
generate No_Lender_Data_FIRM=1 if _merge==1
replace No_Lender_Data_FIRM=0 if No_Lender_Data_FIRM==.
drop _merge
label variable No_Lender_Data_FIRM "FIRM data contains no information on lender´s CVR"
// Need to rename following vars because we´ll import them again for borrowers
rename cvrnr cvrnr_lender 
rename JUR_* JUR_*_lender
rename GF_* GF_*_lender


// Obtain borrower information from FIRM and FIRE
rename cvrnr_borrower cvrnr  
merge m:1 cvrnr year using "${procdata}/fire_panel.dta", keep(match master)
generate No_Borrower_Data_FIRE=1 if _merge==1
replace No_Borrower_Data_FIRE=0 if No_Borrower_Data_FIRE==.
drop _merge
label variable No_Borrower_Data_FIRE "FIRE data contains no information on borrowers´s CVR"

merge m:1 cvrnr year using "${procdata}/firm_panel.dta", keep(match master)
generate No_Borrower_Data_FIRM=1 if _merge==1
replace No_Borrower_Data_FIRM=0 if No_Borrower_Data_FIRM==.
drop _merge
label variable No_Borrower_Data_FIRM "FIRM data contains no information on borrowers´s CVR"

gen borrower_age = year - year(JUR_FRA_DATO) if year(JUR_TIL_DATO) >= year

rename cvrnr cvrnr_borrower
rename JUR_VIRK_FORM JUR_VIRK_FORM_borrower
rename JUR_FRA_DATO JUR_FRA_DATO_borrower
rename JUR_TIL_DATO JUR_TIL_DATO_borrower 
rename GF_NACE_DB07 GF_NACE_DB07_borrower

* add cpi index and DKK-USD exchange rate
merge m:1 year using "${rawdata}/DK_USD.dta", keep(match) nogen keepusing(cpi x_rate)

////////////////////////////////////////////
//////////  Sample selection 
////////////////////////////////////////////

* economically active firms above DST-defined activity limit
keep if GF_BAGATEL == "0"

destring GF_NACE_DB07_lender, replace
destring GF_NACE_DB07_borrower, replace
drop if inrange(GF_NACE_DB07_borrower, 6400, 6600) // Drop loans to financial companies 

*legal entities (remove coops, sole proprietorship, municipalities, NGOs etc.)
keep if inlist(JUR_VIRK_FORM_borrower,10,80,60,30,81,40,70)
		
* revenue cutoff DKK 75K 
drop if GF_OMS < 75000

* drop firms that are not active at the end of a year
drop if JUR_TIL_DATO_borrower<mdy(12,31,year)
drop if JUR_TIL_DATO_lender<mdy(12,31,year)

*(companies the are missing survey information according to JKOD)
drop if jkod=="0" | jkod=="1" | jkod=="2" | jkod=="3" | jkod=="4" | jkod=="5"


destring JUR_HOVED_BRA_DB07_lender, replace

drop if GF_NACE_DB07_lender == . // 
drop if JUR_HOVED_BRA_DB07_lender  == 649210 // mortgage banks
drop if JUR_HOVED_BRA_DB07_lender  >= 990000		//extrateritorial institutions
drop if GF_NACE_DB07_lender >= 8400 & GF_NACE_DB07_lender<8500		//government institutions
drop if GF_NACE_DB07_lender == 6411   //central bank


**********************************************************************
* Add macroeconomic controls and standardize monetary policy shocks 
**********************************************************************

merge m:1 year using "${procdata}/macro_data.dta", nogenerate

local mpshocks JK_mpshocksign JK_eureon3m_hf AL_OIS3M_mpshock AL_OIS1Y_mpshock  ///
	AL_OIS10Y_mpshock AL_DE10Y_mpshock

foreach shock in `mpshocks' {

		qui rename `shock' raw_`shock'
		qui egen `shock' = std(raw_`shock')
		
	}


su `mpshocks' // All MPshocks are now standardized
su raw_* // raw values of all series (in %) are variables ending in _raw

////////////////////////////////////////////
//////////  Generate important additional variables 
////////////////////////////////////////////

// Create id variables
egen id_lender = group(cvrnr_lender)
egen id_borrower = group(cvrnr_borrower)
egen id_borrowerlender = group(cvrnr_borrower cvrnr_lender)
xtset id_borrowerlender year

/// Compute interest rate and other outcome variables
gen intrate = RNT_BLB / (0.5* (REST_GAELD_BLB + L.REST_GAELD_BLB))
gstats winsor intrate, cuts(1 98) replace

gen lndebt = log(REST_GAELD_BLB)  

// Classify banks vs nonbanks
gen byte nonbank_lender=0 if GF_NACE_DB07_lender==6419		//banks
replace nonbank_lender=1 if GF_NACE_DB07_lender>=6420  & GF_NACE_DB07_lender<6700	 //nonbanks financial institutions

label var nonbank_lender "Strict definition of nonbanks (only fin. institutions)"
label def nonbank_lender_all 1 "Nonbank"  0 "Bank"

drop if mi(nonbank_lender) 

// Broad industry classification for borrowers (2-digit NACE codes)
destring GF_NACE_DB07_borrower, replace
gen industry_borrower_coarse = 1 if GF_NACE_DB07_borrower >= 100 & GF_NACE_DB07_borrower<400
replace industry_borrower_coarse = 2 if GF_NACE_DB07_borrower >= 500 & GF_NACE_DB07_borrower<1000
replace industry_borrower_coarse = 3 if GF_NACE_DB07_borrower >= 1000 & GF_NACE_DB07_borrower<3400
replace industry_borrower_coarse = 4 if GF_NACE_DB07_borrower >= 3499 & GF_NACE_DB07_borrower<3599
replace industry_borrower_coarse = 5 if GF_NACE_DB07_borrower >= 3600 & GF_NACE_DB07_borrower<4000
replace industry_borrower_coarse = 6 if GF_NACE_DB07_borrower >= 4100 & GF_NACE_DB07_borrower<4400
replace industry_borrower_coarse = 7 if GF_NACE_DB07_borrower >= 4500 & GF_NACE_DB07_borrower<4800
replace industry_borrower_coarse = 8 if GF_NACE_DB07_borrower >= 4900 & GF_NACE_DB07_borrower<5400
replace industry_borrower_coarse = 9 if GF_NACE_DB07_borrower >= 5500 & GF_NACE_DB07_borrower<5700
replace industry_borrower_coarse = 10 if GF_NACE_DB07_borrower >= 5800 & GF_NACE_DB07_borrower<6400
replace industry_borrower_coarse = 11 if GF_NACE_DB07_borrower >= 6400 & GF_NACE_DB07_borrower<6700
replace industry_borrower_coarse = 12 if GF_NACE_DB07_borrower >= 6800 & GF_NACE_DB07_borrower<6900
replace industry_borrower_coarse = 13 if GF_NACE_DB07_borrower >= 6900 & GF_NACE_DB07_borrower<7600
replace industry_borrower_coarse = 14 if GF_NACE_DB07_borrower >= 7700 & GF_NACE_DB07_borrower<8300
replace industry_borrower_coarse = 15 if GF_NACE_DB07_borrower >= 8400 & GF_NACE_DB07_borrower<8500
replace industry_borrower_coarse = 16 if GF_NACE_DB07_borrower >= 8500 & GF_NACE_DB07_borrower<8600
replace industry_borrower_coarse = 17 if GF_NACE_DB07_borrower >= 8600 & GF_NACE_DB07_borrower<8900
replace industry_borrower_coarse = 18 if GF_NACE_DB07_borrower >= 9000 & GF_NACE_DB07_borrower<9400
replace industry_borrower_coarse = 19 if GF_NACE_DB07_borrower >= 9400 & GF_NACE_DB07_borrower<9700
replace industry_borrower_coarse = 20 if GF_NACE_DB07_borrower >= 9700 & GF_NACE_DB07_borrower<9900
replace industry_borrower_coarse = 21 if GF_NACE_DB07_borrower >= 9900 & GF_NACE_DB07_borrower<100000

label def industry_borrower_short ///
	1 "Agriculture" ///
	2 "Mining" ///
	3 "Manufacturing" ///
	4 "Energy & Utilities" ///
	5 "Water & Waste" ///
	6 "Construction" ///
	7 "Trade & Retail" ///
	8 "Transport" ///
	9 "Hotels & Restaurants" ///
	10 "Information & Telecom" ///
	11 "Finance & Insurance" ///
	12 "Real Estate" ///
	13 "Professional Services" ///
	14 "Business Support" ///
	15 "Public Administration" ///
	16 "Education" ///
	17 "Health & Social" ///
	18 "Arts & Entertainment" ///
	19 "Other Services" ///
	20 "Households" ///
	21 "International Orgs", replace
	
label values industry_borrower_coarse industry_borrower_short
label var industry_borrower_coarse "Broad Structure of NACE Rev.2"


// Industry classification for nonbanks 
destring JUR_HOVED_BRA_DB07_lender, replace
label def lender_type_short ///
641100 "Central Banks" ///
641900 "Commercial Banks" ///
642010 "Financial Holdings" ///
642020 "Non-Financial Holdings" ///
642030 "Mixed Holdings" ///
643010 "Investment Funds" ///
643020 "Money Market Funds" ///
643030 "Investment Companies" ///
643040 "Private Equity & VC" ///
649100 "Financial Leasing" ///
649210 "Mortgage Institutions" ///
649220 "Specialized Finance Companies" ///
649230 "Credit Companies" ///
649240 "Financial Vehicles" ///
649900 "Investment & Wealth Management" ///
651100 "Life Insurance" ///
651200 "Non-Life Insurance" ///
652000 "Reinsurance" ///
653010 "Pension Funds" ///
653020 "Other Pensions" ///
661100 "Market Administration" ///
661200 "Securities Trading" ///
661900 "Financial Auxiliaries" ///
662100 "Risk Assessment" ///
662200 "Insurance Brokers" ///
662900 "Insurance Auxiliaries" ///
663000 "Fund Management", replace

label values JUR_HOVED_BRA_DB07_lender lender_type_short

// interaction terms: nonbank X macro variables 
gen inter1=nonbank_lender*L.JK_mpshocksign
gen inter2=nonbank_lender*L.DK_real_gdp_grate
gen inter3=nonbank_lender*L.DK_gdp_growth_forecast
gen inter4=nonbank_lender*L.DK_inflation_rate
gen inter5=nonbank_lender*L.vix

// interaction terms with alternative MP shocks 
gen inter1_JKHF=nonbank_lender*L.JK_eureon3m_hf 
gen inter1_AL3M=nonbank_lender*L.AL_OIS3M_mpshock
gen inter1_AL1Y=nonbank_lender*L.AL_OIS1Y_mpshock
gen inter1_AL10Y=nonbank_lender*L.AL_OIS10Y_mpshock
gen inter1_AL10YDE=nonbank_lender*L.AL_DE10Y_mpshock

// Create industry-location-size-time (ILST) fixed-effect 
gquantiles size_decile = GF_AT, xtile n(10) by(year)
egen ILST_FE = group(JUR_BEL_KOM_KODE industry_borrower_coarse size_decile year)

// Clean up and save 
order year id_* REST_GAELD_BLB nonbank_*
cap drop _merge


save "${procdata}/URTEVIRK_FIRE_FIRM_03-18.dta", replace 



